package net.comm.group.db;

import org.sakaiproject.db.api.iServiceSql;


/**
 * methods for accessing user data in a database.
 */
public class GroupsServiceSqlMysql implements iServiceSql
{
	/**
	 * 返回根据主键删除语句
	 */
	public String getDeleteSql()
	{
		return "delete from Groups where GroupID=?";
	}

	/**
	 * 返回插入语句
	 */
	public String getInsertSql()
	{
		return "insert into Groups ( groupID,  villageID,  name,  subject, firsort,  secsort,  thiSort," +
				"  pic,  work_location, work_sublocation,  work_Area,  joinNum,  num, ctime,  userID, " +
				" pepoleVal,  partakeVal, contrVal,  type,  useAlbumSize,  albumSize, status,  mtime " +
				") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	}
	
	/**
	 * 返回根据主键修改语句
	 */
	public String getUpdateSql()
	{
		return "update Groups  set villageID=?,  name=?,  subject=?, firsort=?,  secsort=?,  thiSort=?," +
				"  pic=?,  work_location=?, work_sublocation=?,  work_Area=?,  joinNum=?,  num=?, ctime=?,  userID=?, " +
				" pepoleVal=?,  partakeVal=?, contrVal=?,  type=?,  useAlbumSize=?,  albumSize=?, status=?,  mtime=? " +
				" where GroupID=?";
	}

	/**
	 * 返回根据主键修改语句
	 */
	public String getUpdNumSql()
	{
		return "update Groups  set JoinNum=?, PepoleVal=?,  PartakeVal=?," +
				"  ContrVal=?, UseAlbumSize=? where GroupID=?";
	}

	/**
	 * 返回根据主键查询语句
	 */
	public String getbykeySql()
	{
		return "select groupID,  villageID,  name,  subject, firsort,  secsort,  thiSort," +
				"  pic,  work_location, work_sublocation,  work_Area,  joinNum,  num, ctime,  userID, " +
				" pepoleVal,  partakeVal, contrVal,  type,  useAlbumSize,  albumSize, status,  mtime " +
				" from Groups where GroupID=?";
	}
	
	/**
	 * 返回根据群组名称查询语句
	 */
	public String getbyNameSql()
	{
		return "select groupID,  villageID,  name,  subject, firsort,  secsort,  thiSort," +
				"  pic,  work_location, work_sublocation,  work_Area,  joinNum,  num, ctime,  userID, " +
				" pepoleVal,  partakeVal, contrVal,  type,  useAlbumSize,  albumSize, status,  mtime " +
				" from Groups where Name=? and villageID=? ";
	}
	/**
	 * 返回根据会员ID查询语句 
	 */
	public String getByUserIDSql()
	{
		StringBuffer sb =  new StringBuffer("select groupID,  villageID,  name,  subject, firsort,  secsort,  thiSort," +
				"  pic,  work_location, work_sublocation,  work_Area,  joinNum,  num, ctime,  userID, " +
				" pepoleVal,  partakeVal, contrVal,  type,  useAlbumSize,  albumSize, status,  mtime " +
				" from Groups");
		
		sb.append(" where UserID=? ");
				
		return sb.toString();
	}
	/**
	 * 返回根据分类查询语句 
	 */
	public String getByFirsortSql()
	{
		StringBuffer sb =  new StringBuffer("select groupID,  villageID,  name,  subject, firsort,  secsort,  thiSort," +
				"  pic,  work_location, work_sublocation,  work_Area,  joinNum,  num, ctime,  userID, " +
				" pepoleVal,  partakeVal, contrVal,  type,  useAlbumSize,  albumSize, status,  mtime " +
				" from Groups");
		
		sb.append(" where Firsort=? limit ? ");
				
		return sb.toString();
	}
	/**
	 * 返回根据分类查询语句 
	 */
	public String getByCitySql()
	{
		StringBuffer sb =  new StringBuffer("select groupID,  villageID,  name,  subject, firsort,  secsort,  thiSort," +
				"  pic,  work_location, work_sublocation,  work_Area,  joinNum,  num, ctime,  userID, " +
				" pepoleVal,  partakeVal, contrVal,  type,  useAlbumSize,  albumSize, status,  mtime " +
				" from Groups");
		
		sb.append(" where Work_sublocation in ( select Work_sublocation from Groups where GroupID=? ) limit ? ");
				
		return sb.toString();
	}
	
	/**
	 * 查询按firsort分组后的前n条记录
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getGroupByFirSecsortSql()
	{
		StringBuffer sb =  new StringBuffer("select  groupID,  villageID,  name,  subject, firsort,  secsort,  thiSort," +
				"  pic,  work_location, work_sublocation,  work_Area,  joinNum,  num, ctime,  userID, " +
				" pepoleVal,  partakeVal, contrVal,  type,  useAlbumSize,  albumSize, status,  mtime " +
				" from Groups a where villageID=? and Firsort = ? and ? > (select count(*) from Groups g where Secsort = g.Secsort " +
				"and groupID > g.groupID ) order by  a.Secsort ");

		return sb.toString();	
	}		
	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyOrderbyPepoleValSql()
	{
	
		StringBuffer sb =  new StringBuffer("select groupID,  villageID,  name,  subject, firsort,  secsort,  thiSort," +
				"  pic,  work_location, work_sublocation,  work_Area,  joinNum,  num, ctime,  userID, " +
				" pepoleVal,  partakeVal, contrVal,  type,  useAlbumSize,  albumSize, status,  mtime " +
				" from Groups ");
		
		sb.append(" where 1=1  order by PepoleVal desc ");
		sb.append(" limit ? ");
				
		return sb.toString();	
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyOrderbyCTimeSql()
	{
	
		StringBuffer sb =  new StringBuffer("select groupID,  villageID,  name,  subject, firsort,  secsort,  thiSort," +
				"  pic,  work_location, work_sublocation,  work_Area,  joinNum,  num, ctime,  userID, " +
				" pepoleVal,  partakeVal, contrVal,  type,  useAlbumSize,  albumSize, status,  mtime " +
				" from Groups ");
		
		sb.append(" where 1=1  order by CTime desc ");
		sb.append(" limit ? ");
				
		return sb.toString();	
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyPageSql()
	{
	
		StringBuffer sb =  new StringBuffer("select groupID,  villageID,  name,  subject, firsort,  secsort,  thiSort," +
				"  pic,  work_location, work_sublocation,  work_Area,  joinNum,  num, ctime,  userID, " +
				" pepoleVal,  partakeVal, contrVal,  type,  useAlbumSize,  albumSize, status,  mtime " +
				" from Groups ");
		
		sb.append(" where 1=1 ");
		sb.append(" limit ?, ? ");
				
		return sb.toString();	
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecSql()
	{
	
		StringBuffer sb =  new StringBuffer("select groupID,  villageID,  name,  subject, firsort,  secsort,  thiSort," +
				"  pic,  work_location, work_sublocation,  work_Area,  joinNum,  num, ctime,  userID, " +
				" pepoleVal,  partakeVal, contrVal,  type,  useAlbumSize,  albumSize, status,  mtime " +
				" from Groups ");
		return sb.toString();	
	}	

	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyFruserIDSql()
	{
	
		StringBuffer sb =  new StringBuffer("select groupID,  villageID,  name,  subject, firsort,  secsort,  thiSort," +
				"  pic,  work_location, work_sublocation,  work_Area,  joinNum,  num, ctime,  userID, " +
				" pepoleVal,  partakeVal, contrVal,  type,  useAlbumSize,  albumSize, status,  mtime " +
				" from Groups ");
		
		sb.append(" where UserID in ( select FriendID from Friends where userID=? and status=1 ) ");
		sb.append(" limit ?, ? ");
				
		return sb.toString();	
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecmyAddSql()
	{
	
		StringBuffer sb =  new StringBuffer("select groupID,  villageID,  name,  subject, firsort,  secsort,  thiSort," +
				"  pic,  work_location, work_sublocation,  work_Area,  joinNum,  num, ctime,  userID, " +
				" pepoleVal,  partakeVal, contrVal,  type,  useAlbumSize,  albumSize, status,  mtime " +
				" from Groups ");
		
		sb.append(" where GroupID in ( select GroupID from GMember where userID=? and Type in (?,?) and Status = 1 ) ");
		sb.append(" limit ?, ? ");
				
		return sb.toString();	
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecGroupbyFirsortSql()
	{
	
		StringBuffer sb =  new StringBuffer("select Firsort, count(*) as total " +
				" from Groups ");
		
		sb.append(" Group by Firsort ");
				
		return sb.toString();	
	}	

	/**
	 * 返回所有记录数
	 */
	public String getCountSql1()
	{
		return " select count(*) as COUNT from Groups ";
	}


	/**
	 * 返回根据查询条件的记录数
	 */
	public String getCountSql2()
	{
		return "select count(*) as COUNT from Groups where UserID=? ";
	}

	/**
	 * 返回根据查询条件的记录数
	 */
	public String getCountSql3()
	{
		return "select count(*) as COUNT from Groups where UserID in ( select FriendID from Friends where userID=? ) ";
	}
	
	/**
	 * 返回根据查询条件的记录数
	 */
	public String getCountSql4()
	{
		return "select count(*) as COUNT from Groups where GroupID " +
				"in ( select GroupID from GMember where userID=? and Type in (?,?) and Status = 1 ) ";
	}

	/**
	 * @see net.comm.job.db.iServiceSql.impl.UserServiceSql#getMaxInputsForSelectWhereInQueries()
	 */
	public int getMaxInputsForSelectWhereInQueries() {
		// For Oracle, the maximum supported number of expressions in a list is 1000. 
		return 1000;
	}
}
